package org.fao.pims.persistence;

import java.util.*;
import java.sql.*;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.fao.pims.model.Glossary;
import org.fao.pims.model.Taxon;

public class GlossaryDaoImpl  extends BaseDao implements GlossaryDao{

	private static Log log = LogFactory.getLog(GlossaryDaoImpl.class);
	
	private Connection conn = null;
	
	
	public Glossary getTerm(String term) {
		
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        Glossary result = new Glossary();
       
        
        try {
            
            conn = this.getDatasource().getConnection();          
            
            String query = "SELECT * FROM  glossary " +
            					"WHERE term='" + term + "'";
            					
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            
            List resultList = makeBeanList(resultsetToList(rset));
            
            // should return only one term
            if(resultList.size() > 0) {
	         
            	Iterator i = resultList.iterator();
	            
            	while(i.hasNext()) {
	            	
	            	result = (Glossary)i.next();
	            	
	            }
            }
            
            pstmt.close();
            
            conn.close();                       
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            
        }
        finally {
                                    
            try 
            { 
                if( conn != null )
                		conn.close(); 
                
                log.info("Connection closed");                
                
            }
            catch(Exception e) {
                
            	log.error(e.getMessage());
               
            }           
            
            
        }
		
		return result;
	}
	
	public String getDefinition(String term) {
		
		String definition = "";
		PreparedStatement pstmt = null;
        ResultSet rset = null;
        Connection conn = null;
        
       
        
        try {
            
            conn = this.getDatasource().getConnection();          
            
            String query = "SELECT definition FROM  glossary " +
            					"WHERE term='" + term + "'";
            					
            
            pstmt = conn.prepareStatement(query);
            
            rset = pstmt.executeQuery();    
            
            
          
          
            while (rset.next()) {
            	
                definition = rset.getString("definition");
                
                //System.out.println("definition: " + "     " + definition);
            }
          
            
            rset.close();
            
            pstmt.close();
            
            conn.close();                       
            
        } 
        catch(SQLException e) {
            e.printStackTrace();
            
        }
        finally {
                                    
            try 
            { 
                if( conn != null )
                		conn.close(); 
                
                log.info("Connection closed");                
                
            }
            catch(Exception e) {
                
            	log.error(e.getMessage());
               
            }           
            
            
        }
		
		return definition;
		
		
	}
	
	
	protected List makeBeanList(List rows){
		
        List beanList = new ArrayList();
         
        ListIterator rowIterator = rows.listIterator();
        int rowcounter = 0;
        while(rowIterator.hasNext())
        {
            rowcounter++;
            List fieldlist = (List)rowIterator.next();
            ListIterator fieldsIterator = fieldlist.listIterator();
            Object obj = null;
            String type = "";
            String columnName = "";
            Glossary bean = new Glossary();
            
            
            while(fieldsIterator.hasNext())
            {
                TableFieldHandler tfhandler = (TableFieldHandler)fieldsIterator.next();
                obj = tfhandler.getObject();
                type = tfhandler.getType();
                columnName = tfhandler.getColumnName().trim();
                
                if( "ID".equalsIgnoreCase(columnName) )
                {
                    bean.setId( ((Long)obj).intValue() );                
                }                
                if( "TERM".equalsIgnoreCase(columnName) )
                {
                    bean.setTerm( (String)obj );
                }
                if( "ABBREVIATION".equalsIgnoreCase(columnName) )
                {
                    bean.setAbbreviation( (String)obj );                    
                }
                if( "CATEGORY".equalsIgnoreCase(columnName) )
                {
                    bean.setCategory( (String)obj );                    
                }
                if( "SUBCATEGORY".equalsIgnoreCase(columnName) )
                {
                    bean.setSubcategory( (String)obj );                    
                }
                if( "DEFINITION".equalsIgnoreCase(columnName) )
                {
                    bean.setDefinition( (String)obj );                    
                }
                if( "DEFINITION1".equalsIgnoreCase(columnName) )
                {
                    bean.setDefinition1( (String)obj );                    
                }
                if( "DEFINITION2".equalsIgnoreCase(columnName) )
                {
                    bean.setDefinition2( (String)obj );                    
                }
                if( "DEFINITION3".equalsIgnoreCase(columnName) )
                {
                    bean.setDefinition3( (String)obj );                    
                }
                if( "LINGUISTICS".equalsIgnoreCase(columnName) )
                {
                    bean.setLinguistics( (String)obj );
                }
                if( "REFERENCE1".equalsIgnoreCase(columnName) )
                {
                    bean.setReference1( (String)obj );
                }
                if( "REFERENCE2".equalsIgnoreCase(columnName) )
                {
                    bean.setReference2( (String)obj );
                }
                if( "REFERENCE3".equalsIgnoreCase(columnName) )
                {
                    bean.setReference3( (String)obj );
                }
                if( "GEN_SUBJECT_CAT".equalsIgnoreCase(columnName) )
                {
                    bean.setGeneralSubjectCategory( (String)obj );
                }
                if( "RELIABILITY".equalsIgnoreCase(columnName) )
                {
                    bean.setReliability( (String)obj );
                }
                if( "TRANSLATION".equalsIgnoreCase(columnName) )
                {
                    bean.setTranslation( (String)obj );
                }
                if( "TAXONOMIC_CODE".equalsIgnoreCase(columnName) )
                {
                    bean.setTaxonomicCode( (String)obj );
                }                
                                                           
                         
            }
            
            beanList.add(bean);
            
         }
        
        return beanList;
        
    }

}
